package com.hnkywl.sts.dao.jsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cggl.Zjbcd;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-6-8
 * Time: 上午1:07
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class ZjbcdDao extends SimpleHibernateTemplate<Zjbcd, Long> {
    public Page findZjbcdSearch(Long cgjsdId, int pageNo, int pageSize) {
        Page page = new Page();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(Zjbcd.class)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx", "cgdmx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx.gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lb", "lb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("lb.id").as("lb.id"))
                        .add(Property.forName("lb.z").as("lb.z"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("je").as("je"))
                        .add(Property.forName("cgdmx.id").as("cgdmx.id"))
                        .add(Property.forName("cgd.id").as("cgd.id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                ).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (cgjsdId != null)
            criteria.add(Restrictions.eq("jsd.id", cgjsdId));
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }

    public List<Map> findCgjddmxSearch(Long cgjsdId) {
        Criteria criteria = getSession().createCriteria(Zjbcd.class)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx", "cgdmx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx.gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lb", "lb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("lb.id").as("lb.id"))
                        .add(Property.forName("lb.z").as("lb.z"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("je").as("je"))
                        .add(Property.forName("cgdmx.id").as("cgdmx.id"))
                        .add(Property.forName("cgd.id").as("cgd.id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.add(Restrictions.eq("jsd.id", cgjsdId));
        RegionDao.createDataRegion(criteria);
        return criteria.list();
    }


    public Page findZjbcdList(String wzbm, String wzmc, Long gysId, Long sbgsId, Long sbbmId, Long sbrId, int pageNo, int pageSize) {
        Page page = new Page();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(Zjbcd.class)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createOrgan", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createDepartment", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx", "cgdmx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx.gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lb", "lb", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("lb.id").as("lb.id"))
                        .add(Property.forName("lb.z").as("lb.z"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Projections.sqlProjection("this_.je as \"je\"", new String[]{"je"}, new Type[]{Hibernate.DOUBLE}))
                        .add(Property.forName("cgdmx.id").as("cgdmx.id"))
                        .add(Projections.sqlProjection("this_.gys_id as \"gys.id\"", new String[]{"gys.id"}, new Type[]{Hibernate.LONG}))
                        .add(Property.forName("gys.qc").as("gys.qc"))
                        .add(Property.forName("cgd.id").as("cgd.id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                        .add(Property.forName("jsd.jsdh").as("jsd.jsdh"))
                        .add(Property.forName("processInstanceId").as("processInstanceId"))
                        .add(Property.forName("zt").as("zt"))
                ).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(wzbm))
            criteria.add(Restrictions.like("gg.code", wzbm, MatchMode.START));
        if (StringUtils.isNotEmpty(wzmc))
            criteria.add(Restrictions.eq("gg.name", wzmc));
        if (gysId != null)
            criteria.add(Restrictions.eq("gys.id", gysId));
        if (sbgsId != null)
            criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        if (sbbmId != null)
            criteria.add(Restrictions.eq("sbbm.id", sbbmId));
        if (sbrId != null)
            criteria.add(Restrictions.eq("sbr.id", sbrId));
        criteria.add(Restrictions.ne("je", 0.0));
        criteria.add(Restrictions.or(Restrictions.or(Restrictions.isNotNull("jsd"), Restrictions.eq("sftzsc", true)), Restrictions.isNotNull("oldId")));
        //RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }


    public List<Map> findSumZjbcd(String wzbm, String wzmc, Long gysId, Long sbgsId, Long sbbmId, Long sbrId) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = "select sum(t.je) as \"je\" from T_BIZ_ZJDCD t " +
                "left outer join T_BIZ_CGDMX cgdmx6_ on t.CGDMX_ID=cgdmx6_.id left outer join T_SYS_GG gg7_ on cgdmx6_.GG_ID=gg7_.id ";

        if (StringUtils.isNotEmpty(wzbm)) {
            condition.add(" gg7_.code like :wzbm ");
            param.put("wzbm", wzbm + "%");
        }

        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" gg7_.name like :wzmc");
            param.put("wzmc", wzbm + "%");
        }

        if (gysId != null) {
            condition.add(" t.gys_id=:gys_id ");
            param.put("gys_id", gysId);
        }

        if (sbgsId != null) {
            condition.add(" t.CREATEORGAN_ID=:sbgs_id ");
            param.put("sbgs_id", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" t.CREATEDEPARTMENT_ID=:sbbm_id ");
            param.put("sbbm_id", sbbmId);
        }
        if (sbrId != null) {
            condition.add(" t.CREATEUSER_ID=:sbr_id ");
            param.put("sbr_id", sbrId);
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";
        String dataRegion = RegionDao.createSQLDataRegion(null, "t");
        sql += " where t.deleted!=1 and t.je<>0 and (t.JSD_ID is not null or t.sftzsc=1 or t.oldid is not null)  and " + whereSql + dataRegion;
        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public Page findZjbcdSearchWl(Long wljsdId, int pageNo, int pageSize) {
        Page page = new Page();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(Zjbcd.class)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx", "cgdmx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgdmx.gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lb", "lb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("lb.id").as("lb.id"))
                        .add(Property.forName("lb.z").as("lb.z"))
                        .add(Property.forName("gg.code").as("wzgg.code"))
                        .add(Property.forName("gg.name").as("wzgg.name"))
                        .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                        .add(Property.forName("gg.dw").as("wzgg.dw"))
                        .add(Property.forName("je").as("je"))
                        .add(Property.forName("cgdmx.id").as("cgdmx.id"))
                        .add(Property.forName("cgd.id").as("cgd.id"))
                        .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                ).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (wljsdId != null)
            criteria.add(Restrictions.eq("jsd.id", wljsdId));
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }

    public Page<Map> findZjpcdList(Long gysId, Long sbgsId, Long sbbmId) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sqlBase = "select zjbcd.* " +
                "  from (select (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.createorgan_id) gsjc, " +
                "  (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.createdepartment_id) bmjc, " +
                "  (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.gys_id) gys, jsd.createorgan_id as gsid, " +
                "  jsd.createdepartment_id as bmid, jsd.gys_id as gysid, (nvl(sum(zj1.je),0)+nvl(sum(zj6.je),0)) qcje, " +
                "  nvl(sum(zj2.je), 0) je2, " +
                "  nvl(sum(zj3.je), 0) je3, " +
                "  nvl(sum(zj4.je), 0) je4, " +
                "  nvl(sum(zj5.je), 0) bqfp ,0 as qmye " +
                "  from T_BIZ_ZJDCD jsd" +
                "  left outer join T_BIZ_ZJDCD zj1 on zj1.id = jsd.id and zj1.date_created <= to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj1.deleted != 1 and zj1.zt ！= 'ysy' " +
                "  left outer join T_BIZ_ZJDCD zj6 inner join T_BIZ_ZJBCDRZ rz6 on zj6.id=rz6.zjbcd_id and rz6.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  on zj6.id = jsd.id and zj6.date_created <= to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj6.deleted != 1 and zj6.zt = 'ysy' " +
                "  left outer join T_BIZ_ZJDCD zj2 on zj2.id = jsd.id" +
                "  and zj2.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj2.deleted != 1 and zj2.sjzdz_lb_id = 50" +
                "  left outer join T_BIZ_ZJDCD zj3 on zj3.id = jsd.id" +
                "  and zj3.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj3.deleted != 1" +
                "  and zj3.sjzdz_lb_id in (51,52)" +
                "  left outer join T_BIZ_ZJDCD zj4" +
                "  on zj4.id = jsd.id" +
                "  and zj4.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj4.deleted != 1 and zj4.sjzdz_lb_id = 281" +
                "  left outer join T_BIZ_ZJDCD zj5 inner join T_BIZ_ZJBCDRZ rz5 on zj5.id=rz5.zjbcd_id and rz5.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  on zj5.id = jsd.id and zj5.deleted != 1  and zj5.zt = 'ysy'" +
                "  where jsd.deleted != 1 and (jsd.jsd_id is not null or jsd.sftzsc=1 or jsd.oldid is not null)" +
                "  group by jsd.createorgan_id, jsd.createdepartment_id, jsd.gys_id order by jsd.createorgan_id) zjbcd" +
                " where (zjbcd.qcje!=0 or zjbcd.je2!=0 or zjbcd.je3!=0 or zjbcd.je4!=0) ";

        String sqlCountBase = " (select  (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.createorgan_id) gsjc, " +
                "  (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.createdepartment_id) bmjc, " +
                "  (select gs.jc from T_SYS_GSJG gs where gs.id = jsd.gys_id) gys, jsd.createorgan_id as gsid, " +
                "  jsd.createdepartment_id as bmid, jsd.gys_id as gysid, (nvl(sum(zj1.je),0)+nvl(sum(zj6.je),0)) qcje, " +
                "  nvl(sum(zj2.je), 0) je2, " +
                "  nvl(sum(zj3.je), 0) je3, " +
                "  nvl(sum(zj4.je), 0) je4, " +
                "  nvl(sum(zj5.je), 0) bqfp,0 as qmye " +
                "  from T_BIZ_ZJDCD jsd" +
                "  left outer join T_BIZ_ZJDCD zj1 on zj1.id = jsd.id and zj1.date_created <= to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj1.deleted != 1 and zj1.zt ！= 'ysy' " +
                "  left outer join T_BIZ_ZJDCD zj6 inner join T_BIZ_ZJBCDRZ rz6 on zj6.id=rz6.zjbcd_id and rz6.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  on zj6.id = jsd.id and zj6.date_created <= to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj6.deleted != 1 and zj6.zt = 'ysy' " +
                "  left outer join T_BIZ_ZJDCD zj2 on zj2.id = jsd.id " +
                "  and zj2.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj2.deleted != 1 and zj2.sjzdz_lb_id = 50" +
                "  left outer join T_BIZ_ZJDCD zj3 on zj3.id = jsd.id" +
                "  and zj3.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj3.deleted != 1" +
                "  and zj3.sjzdz_lb_id in (51,52)" +
                "  left outer join T_BIZ_ZJDCD zj4" +
                "  on zj4.id = jsd.id" +
                "  and zj4.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  and zj4.deleted != 1 and zj4.sjzdz_lb_id = 281" +
                "  left outer join T_BIZ_ZJDCD zj5 inner join T_BIZ_ZJBCDRZ rz5 on zj5.id=rz5.zjbcd_id and rz5.date_created > to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')" +
                "  on  zj5.id = jsd.id" +
                "  and zj5.deleted != 1  and zj5.zt = 'ysy'" +
                "  where jsd.deleted != 1 and (jsd.jsd_id is not null or jsd.sftzsc=1 or jsd.oldid is not null)" +
                "  group by jsd.createorgan_id, jsd.createdepartment_id, jsd.gys_id  ) zjbcd" +
                " where (zjbcd.qcje!=0 or zjbcd.je2!=0 or zjbcd.je3!=0 or zjbcd.je4!=0)  ";

        if (gysId != null) {
            condition.add(" zjbcd.gysid=:gysId ");
            param.put("gysId", gysId);
        }

        if (sbgsId != null) {
            condition.add(" zjbcd.gsid=:sbgsId ");
            param.put("sbgsId", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" zjbcd.bmid=:sbbmId ");
            param.put("sbbmId", sbbmId);
        }


        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

//        String countSql = "select count(1) from " + sqlCountBase + whereSql;
        String resultSql = sqlBase + whereSql;


//        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(10000);
//        if (count.intValue() != 0) {
        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        page.setResult(list);
//        } else {
//            page.setResult(new ArrayList<Map>());
//        }
        return page;
    }
}
